#!/use/bin/python
# coding=utf-8
# 主动服务 同步经验值，从ck到mysql
from queue import Queue
from datetime import date, timedelta
import pymysql
from dbutils.pooled_db import PooledDB
import sys
import time, datetime
import pandas as pd
import requests
import json
from tabulate import tabulate
from datetime import datetime

now = datetime.now()
curTimeYmdHms = now.strftime("%Y-%m-%d %H:%M:%S")
yesterdayYmd = (date.today() + timedelta(days=-1)).strftime("%Y-%m-%d")
yesterdayYYMMdd = (date.today() + timedelta(days=-1)).strftime("%Y%m%d")
yesterdayYYMMdd = yesterdayYYMMdd[2:]
todayYmd = date.today().strftime("%Y-%m-%d")

env = "测试环境,每日咨询弹窗转化步骤"
execRules = (

    {
        "sql": f"""
       select count(DISTINCT(t.active_id)) '设备数' from log_common_service_{yesterdayYYMMdd} t where t.`key` = 'askTimeDeviceOn' 
        """,
        "title": "3.服务范围内开机设备量",
        "db":2
    },
    {
        "sql": f"""
        select count(DISTINCT(t.active_id)) '设备数' from log_common_service_{yesterdayYYMMdd} t where t.`key` = 'askPreStart' 
 and (JSON_EXTRACT(t.msg,'$.service') ='ZJKJ' or JSON_EXTRACT(t.msg,'$.service') is null ) 
        """,
        "title": "4.询问弹窗准备弹窗",
        "db":2
    },
    {
        "sql": f"""
        select count(1) '次数',count(DISTINCT(t.active_id)) '设备数' from log_common_service_{yesterdayYYMMdd} t where t.`key` = 'askShow' 
and JSON_EXTRACT(t.msg,'$.result') =1 and (JSON_EXTRACT(t.msg,'$.service') ='ZJKJ' or JSON_EXTRACT(t.msg,'$.service') is null ) 
        """,
        "title": "5.1.询问弹窗显示成功",
        "db":2
    },
    {
        "sql": f"""
        select JSON_EXTRACT(t.msg,'$.reason')  '原因',count(1) '次数'  from log_common_service_{yesterdayYYMMdd} t where t.`key` = 'askShow' 
 and JSON_EXTRACT(t.msg,'$.result') =0 and (JSON_EXTRACT(t.msg,'$.service') ='ZJKJ' or JSON_EXTRACT(t.msg,'$.service') is null )  group by JSON_EXTRACT(t.msg,'$.reason')
        """,
        "title": "5.2.询问弹窗显示失败",
        "db":2
    },
    {
        "sql": f"""
        select tt.原因, count(1) '次数' from (
select JSON_EXTRACT(t.msg,'$.reason')  '原因',count(1) '次数',t.active_id  from log_common_service_{yesterdayYYMMdd} t where t.`key` = 'askShow' 
 and JSON_EXTRACT(t.msg,'$.result') =0 and (JSON_EXTRACT(t.msg,'$.service') ='ZJKJ' or JSON_EXTRACT(t.msg,'$.service') is null ) 
 group by JSON_EXTRACT(t.msg,'$.reason'),t.active_id) tt group by tt.原因 order by count(1) desc
        """,
        "title": "5.2.询问弹窗显示失败(激活id去重)",
        "db":2
    },
    {
        "sql": f"""
        select (case when tt.choose = 1 then '同意特定时间' when tt.choose = 2 then '返回' when tt.choose = 3 then '自动消失' else '其他' end ) as '结果' ,num '次数' from (
select JSON_EXTRACT(t.msg,'$.choose')  choose ,count(1) num from log_common_service_{yesterdayYYMMdd} t where t.`key` = 'askA' 
 and (JSON_EXTRACT(t.msg,'$.service') ='ZJKJ' or JSON_EXTRACT(t.msg,'$.service') is null )  group by  JSON_EXTRACT(t.msg,'$.choose')) tt
        """,
        "title": "6弹窗后用户选择",
        "db":2
    },
    {
        "sql": f"""
        select count(1) from (
    select count(1),t.active_id from log_common_service_{yesterdayYYMMdd} t where t.`key` = 'createTask'  	 
         and t.active_id in (select tt.active_id from log_common_service_{yesterdayYYMMdd} tt where tt.`key` = 'askA' 
         and (JSON_EXTRACT(tt.msg,'$.service') ='ZJKJ' or JSON_EXTRACT(tt.msg,'$.service') is null ) 
         and DATE_FORMAT(tt.create_time, '%Y-%m-%d') ='{yesterdayYmd}' and JSON_EXTRACT(tt.msg,'$.choose') =1 )group by t.active_id) ttt
        """,
        "title": "7成功创建定时器任务",
        "db":2
    },
)


def getMysqlDataConnection():
    pool = PooledDB(pymysql, 2, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407, cursorclass=pymysql.cursors.DictCursor)  # 1为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

def getMysqlConfConnection():
    pool = PooledDB(pymysql, 2, host='172.20.150.109', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_conf', port=3307, cursorclass=pymysql.cursors.DictCursor)  # 1为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

from datetime import datetime

now = datetime.now()
curTime = now.strftime("%Y-%m-%d %H:%M:%S")

url = "https://open.feishu.cn/open-apis/bot/v2/hook/4b102c84-338a-4bef-87f9-441ecd3f737b"

msgs = []
for item in execRules:
    if item["db"] == 1:
        conn, cur = getMysqlConfConnection()
    else:
        conn, cur = getMysqlDataConnection()
    sql = item["sql"]
    #print(f"执行SQL:{sql}")
    df = pd.read_sql(item["sql"], con=conn)
    title = item["title"]
    msg = tabulate(df, headers='keys', tablefmt='pretty')
    str = """ 
{title}
{msg}
    """.format(
        msg=msg,
        title=title
    )
    msgs.append(str)

msg_content = "{env}\n{curTime}\n{msg}".format(env=env, curTime=yesterdayYmd, msg="".join(msgs))

print(msg_content)

payload = {
    "msg_type": "text",
    "content": {
        "text": msg_content
    }
}

headers = {
    'Content-Type': 'application/json'
}

data = json.dumps(payload)

# 按照utf-8编码成字节码
data = data.encode("utf-8")

#response = requests.post(url, data=data, headers=headers)
#print(response.text)
